'''
-- 微信小程序用户信息扩展表
CREATE TABLE IF NOT EXISTS wx_user_info_extend (
    user_id INT PRIMARY KEY, -- 用户ID
    real_name VARCHAR(50), -- 真实姓名
    sex VARCHAR(10), -- 性别
    e_mail VARCHAR(50), -- 邮箱
    phone_number VARCHAR(50), -- 用户手机号
    -- 工作单位
    work_unit VARCHAR(50),
    -- 职位
    job VARCHAR(50),
    -- 所在城市
    city VARCHAR(50),
    -- 所在省份
    province VARCHAR(50),
    -- 工作地址
    address VARCHAR(100),


    -- 单位性质
    unit_property VARCHAR(50),
    -- 所属科转链条领域
    field VARCHAR(50),
    -- 工作单位介绍
    unit_introduction VARCHAR(200),
    -- 自己核心竞争力
    competitive_strength VARCHAR(200),
    -- 自己经常活动的城市
    activity_city VARCHAR(50),
    -- 自己能够为联盟成员提供什么资源或自己擅长领域
    resource VARCHAR(200),
    -- 需要联盟提供哪些资源或帮助
    help VARCHAR(200),
    -- 推荐人id
    recommend_user_id INT,
    -- 提交时间
    submit_time TIMESTAMP DEFAULT now(),
    -- 添写状态
    write_status BOOLEAN DEFAULT false,
     -- 头像名称
    avatar_name VARCHAR(50) unique,
    -- qrcode名称
    qrcode_name VARCHAR(50) unique,

    FOREIGN KEY (user_id) REFERENCES wx_user(user_id),
    FOREIGN KEY (recommend_user_id) REFERENCES wx_user(user_id)

);'''


import logging
import psycopg2


logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
class WxUserInfoExtendTableMethods: 

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    # user_id 必须存在, 查询user_id对应的用户信息
    def query_user_info_by_user_id(self, user_id):
        sql = "SELECT * FROM wx_user_info_extend WHERE user_id = %s"
        values = (user_id,)
        self.cursor.execute(sql, values)
        result = self.cursor.fetchone()
        if result:
            logger.info(f"query user info success user_id: {user_id}")
            return result
        else:
            return None
        
    # 查看推荐人id对应的所有用户信息
    def query_user_info_by_recommend_user_id(self, recommend_user_id,  page, limit):
        sql = "SELECT * FROM wx_user_info_extend WHERE recommend_user_id = %s LIMIT %s OFFSET %s"
        values = (recommend_user_id, limit, (page-1)*limit)
        self.cursor.execute(sql, values)
        result = self.cursor.fetchall()
        return result
    
    # 验证电话号码是否存在
    def query_phone_number_exist(self, phone_number):
        sql = "SELECT * FROM wx_user_info_extend WHERE phone_number = %s"
        values = (phone_number,)
        self.cursor.execute(sql, values)
        result = self.cursor.fetchone()
        if result:
            return True
        else:
            return False



    def base_info_insert(self, user_id, real_name, sex, e_mail, work_unit, job, city, province, address, department, phone_number): # 基本信息插入

        if not self.query_user_info_by_user_id(user_id):
            sql = "INSERT INTO wx_user_info_extend (user_id, real_name, sex, e_mail, work_unit, job, city, province, address, department, phone_number) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            values = (user_id, real_name, sex, e_mail, work_unit, job, city, province, address, department, phone_number)
            self.cursor.execute(sql, values)
            self.conn.commit()
            logger.info(f"insert base info success user_id: {user_id}")
        else:
            sql = "UPDATE wx_user_info_extend SET real_name = %s, sex = %s, e_mail = %s, work_unit = %s, job = %s, city = %s, province = %s, address = %s, department = %s, phone_number = %s WHERE user_id = %s"
            values = (real_name, sex, e_mail, work_unit, job, city, province, address, department, phone_number, user_id)
            self.cursor.execute(sql, values)
            self.conn.commit()
            logger.info(f"update base info success user_id: {user_id}")

    # 工作信息插入及需求信息插入
    def work_info_insert(self, user_id, unit_property, field, unit_introduction, competitive_strength, activity_city, resource, help, recommend_user_id, write_status=True): # 工作信息插入

        if not self.query_user_info_by_user_id(user_id):
            sql = "INSERT INTO wx_user_info_extend (user_id, unit_property, field, unit_introduction, competitive_strength, activity_city, resource, help, recommend_user_id, write_status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            values = (user_id, unit_property, field, unit_introduction, competitive_strength, activity_city, resource, help, recommend_user_id, write_status)
            self.cursor.execute(sql, values)
            self.conn.commit()
            logger.info(f"insert work info success user_id: {user_id}")
        else:
            sql = "UPDATE wx_user_info_extend SET unit_property = %s, field = %s, unit_introduction = %s, competitive_strength = %s, activity_city = %s, resource = %s, help = %s, recommend_user_id = %s, write_status = %s WHERE user_id = %s"
            values = (unit_property, field, unit_introduction, competitive_strength, activity_city, resource, help, recommend_user_id, write_status, user_id)
            self.cursor.execute(sql, values)
            self.conn.commit()
            logger.info(f"update work info success user_id: {user_id}")

    # 推荐人查看被推荐人数据
    def recommend_user_view_data(self, recommend_user_id): 

        sql = "SELECT (real_name, job, city) FROM wx_user_info_extend WHERE recommend_user_id = %s"
        values = (recommend_user_id,)
        self.cursor.execute(sql, values)
        result = self.cursor.fetchall()

        return result
    
    # 添加头像名称
    def add_avatar_name(self, user_id, avatar_name):
        sql = "UPDATE wx_user_info_extend SET avatar_name = %s WHERE user_id = %s"
        values = (avatar_name, user_id)
        self.cursor.execute(sql, values)
        self.conn.commit()
        logger.info(f"add avatar name success user_id: {user_id}")

    # 添加头像名称
    def add_wx_QR_code(self, user_id, QR_code):
        sql = "UPDATE wx_user_info_extend SET qrcode_name = %s WHERE user_id = %s"
        values = (QR_code, user_id)
        self.cursor.execute(sql, values)
        self.conn.commit()
        logger.info(f"add QR code success user_id: {user_id}")

    def query_user_total_count(self):
        sql = "SELECT COUNT(*) FROM wx_user_info_extend"
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result['count']





        